116

Beginner’s Guide to Code Algorithms

116

STEP 5

  For i =​ 1 To LabelObjectNumber

    If LinesInLabel(i) > 3 Then

      n =​ n +​ 2

        Set rng =​ Range(Cells(2, n), Cells(2, n +​ 1))

        With rng

          Set MyRct =​ ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal,

.Left, .Top, .Width, .Height)

          MyRct.Name =​ “Legend-​” & i

          MyRct.TextFrame2.TextRange.Characters.Text =​ “FROM-​”

&           Mid(LabelObject(i), 1, InStr(LabelObject(i), “-​”) -​ 1)

          MyRct.TextFrame2.TextRange.Characters.Font.Size =​ 10

          MyRct.TextFrame2.VerticalAnchor =​ msoAnchorBottom

          MyRct.TextFrame2.TextRange.ParagraphFormat.

Alignment =​ msoAlignLeft

          MyRct.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.

RGB =​ RGB(255, 0, 0)

          MyRct.TextFrame2.AutoSize =​ msoAutoSizeShapeToFitText

          MyRct.Fill.ForeColor.RGB =​ RGB(50, 50, 0) ‘ highlight

          MyRct.Line.ForeColor.RGB =​ RGB(20, 20, 20)

      End With

    ActiveSheet.Shapes.Range(Array(“Legend-​” & i)).Select

    LegendLeft =​ Selection.ShapeRange.Left

    LegendTop =​ Selection.ShapeRange.Top

    ActiveSheet.Shapes.Range(Array(LabelObject(i))).Select

    Selection.ShapeRange.Left =​ LegendLeft +​ 0

    Selection.ShapeRange.Top =​ LegendTop +​ 35

    ActiveSheet.Shapes.Range(Array(Mid(LabelObject(i), 1, InStr(LabelObject(i), “-​”)

-​ 1))).Select

    Selection.ShapeRange.Line.Style =​ msoLineThickBetweenThin

    Selection.ShapeRange.Line.Weight =​ 10

  End If

    Next

  :

:

End Sub ‘Draw

7.2  CONCLUSION

This chapter discusses simple ways to automate drawing of pictures using Microsoft’s

drawing tool that is embedded in Excel.

The tools explored here allow you to:

• Add worksheets in a workbook

• Control a shape such as square, rectangle, or circle

• Draw arrows

• Connect two shapes with arrows

• Change the weights of arrows